import promisePool from './Pool.js'
import dayjs from 'dayjs'

export default class UserDao {
    
    static async insert (user) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            //开启事务
            await connection.beginTransaction()

            //获取对象中属性值不为null的键,值
            let keys = []
            let vals = []
            for (const key of Reflect.ownKeys(user)) {
                if (user[key] !== null && key !== 'role_ids') {
                    keys.push(key)
                    vals.push(user[key])
                }
            }

            //遍历拼接sql
            let sql = 'insert into `my_user` ('
            let sql_fields = ''
            let sql_vals = ''

            for (let i = 0; i < keys.length; i++) {
                i == keys.length - 1 ? sql_fields += '??)' : sql_fields += '??,'
                i == keys.length - 1 ? sql_vals += '?)' : sql_vals += '?,'
            }

            sql += sql_fields
            sql += ' values ('
            sql += sql_vals
        
            let result = await connection.query(sql, [...keys, ...vals])

            //更新用户角色关联表
            if (user.role_ids) {
                let promise_arr = []
                for (const id of user.role_ids) {
                    promise_arr.push(connection.query('insert into `user_role` (`user_id`, `role_id`) values (?, ?)', [result[0].insertId, id]))
                }
                await Promise.all(promise_arr)
            }
            
            //提交事务
            await connection.commit()

            return result[0].affectedRows
        } catch (error) {
            //事务回滚
            if (connection) await connection.rollback()
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async delete (user) {
        let connection = null
        try {
            connection = await promisePool.getConnection()
            
            let result = await connection.query('update `my_user` set `user_status` = 2 where `id` = ?', user.id)

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async update (user) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            //开启事务
            await connection.beginTransaction()

            //获取对象中属性值不为null的键,值对
            let keys = []
            let key_val = []
            for (const key of Reflect.ownKeys(user)) {
                if (user[key] !== null && key !== 'role_ids' && key !== 'id') {
                    keys.push(key)
                    key_val.push(key)
                    key_val.push(user[key])
                }
            }

            //遍历拼接sql
            let sql = 'update `my_user` set '
            let sql_sets = ''

            for (let i = 0; i < keys.length; i++) {
                i == keys.length - 1 ? sql_sets += '?? = ?' : sql_sets += '?? = ?,'
            }

            sql += sql_sets
            sql += ' where id = ?'
        
            let result = await connection.query(sql, [...key_val, user.id])

            //更新用户角色关联表
            if (user.role_ids) {
                await connection.query('delete from `user_role` where `user_id` = ?', user.id)

                let promise_arr = []
                for (const id of user.role_ids) {
                    promise_arr.push(connection.query('insert into `user_role` (`user_id`, `role_id`) values (?, ?)', [user.id, id]))
                }
                await Promise.all(promise_arr)
            }
            
            //提交事务
            await connection.commit()

            return result[0].affectedRows
        } catch (error) {
            //事务回滚
            if (connection) await connection.rollback()
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectById (id) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [[user]] = await connection.query('select `id`, `username`, `password`, `head_img`, `name`, `intro`, `sex`, `birthday`, `user_status` from `my_user` where `id` = ?', id)

            user.birthday = dayjs(user.birthday).format('YYYY-MM-DD')

            let [role_ids] = await connection.query('select `role_id` from `user_role` where `user_id` = ?', id)
            user.role_ids = role_ids

            return user
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectByLimit (start, end) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [users] = await connection.query('select `id`, `username`, `password`, `head_img`, `name`, `intro`, `sex`, `birthday`, `user_status` from `my_user` limit ?, ' + end, start)

            let promise_arr = []
            for (const user of users) {
                user.birthday = dayjs(user.birthday).format('YYYY-MM-DD')
                promise_arr.push(connection.query('select `role_id` from `user_role` where `user_id` = ?', user.id))
            }
            let results = await Promise.all(promise_arr)
            for (let i = 0; i < users.length; i++) {
                let role_ids = []
                for (const item of results[i][0]) {
                    role_ids.push(item.role_id)
                }
                users[i].role_ids = role_ids
            }

            return users
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectCount () {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let result = await connection.query('select count(id) as total from `my_user`')

            return result[0][0]
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectByUsernameAndPassword (username, password) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [[user]] = await connection.query('select `id`, `username`, `password`, `head_img`, `name`, `intro`, `sex`, `birthday`, `user_status` from `my_user` where `username` = ? and `password` = ?', [username, password])

            user.birthday = dayjs(user.birthday).format('YYYY-MM-DD')

            let [role_ids] = await connection.query('select `role_id` from `user_role` where `user_id` = ? order by `role_id`', user.id)
            role_ids = role_ids.map(obj => obj.role_id)
            
            let promise_arr = []
            let promise_arr2 = []
            for (const role_id of role_ids) {
                promise_arr.push( connection.query('select `permission_id` from `role_permission` where `role_id` = ?', role_id) )
                promise_arr2.push( connection.query('select `id`, `name`, `menuName`, `menuIndex` from `role` where `id` = ? ', role_id) )
            }
            let results = await Promise.all(promise_arr)
            let results2 = await Promise.all(promise_arr2)

            let permission_ids = []
            for (const result of results) {
                permission_ids = permission_ids.concat( result[0] )
            }
            permission_ids = permission_ids.map(obj => obj.permission_id)

            let roles = []
            for (const result of results2) {
                roles.push( result[0][0] )
            }

            user.role_ids = role_ids
            user.roles = roles
            user.permission_ids = Array.from( new Set(permission_ids) )

            return user
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }



    //粉丝相关
    static async insertFan (user_id, fan_id) {
        if (user_id === fan_id) return new Error('user_id = fan_id')

        let connection = null
        try {
            connection = await promisePool.getConnection()

            let result = await connection.query('insert into `fan` (`user_id`, `fan_id`) values (?, ?)', [user_id, fan_id])

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async deleteFan (user_id, fan_id) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let result = await connection.query('delete from `fan` where `user_id` = ? and `fan_id` = ?', [user_id, fan_id])

            return result[0].affectedRows
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectFollow (user_id) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [fans] = await connection.query('select `user_id` from `fan` where `fan_id` = ?', user_id)

            let promise_arr = []
            for (const fan of fans) {
                promise_arr.push(connection.query('select `id`, `username`, `password`, `head_img`, `name`, `intro`, `sex`, `birthday`, `user_status` from `my_user` where `id` = ?', fan.user_id))
            }
            let results = await Promise.allSettled(promise_arr)
            if (results[0].status === 'rejected') throw results[0].reason

            let users = []
            for (const result of results) {
                users.push(result.value[0][0])
            }

            return users
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }

    static async selectFan (user_id) {
        let connection = null
        try {
            connection = await promisePool.getConnection()

            let [fans] = await connection.query('select `fan_id` from `fan` where `user_id` = ?', user_id)

            let promise_arr = []
            for (const fan of fans) {
                promise_arr.push(connection.query('select `id`, `username`, `password`, `head_img`, `name`, `intro`, `sex`, `birthday`, `user_status` from `my_user` where `id` = ?', fan.user_id))
            }
            let results =  await Promise.allSettled(promise_arr)
            if (results[0].status === 'rejected') throw results[0].reason

            let users = []
            for (const result of results) {
                users.push(result.value[0][0])
            }

            return users
        } catch (error) {
            return error
        } finally {
            if (connection) connection.release()
        }
    }
}